package cn.wizzer.app.web.modules.controllers.platform.bus.reports;

import cn.wizzer.app.bus.modules.models.BusinessFileInfo;
import cn.wizzer.app.bus.modules.models.YJbxx;
import cn.wizzer.app.bus.modules.models.YJbxxLzxx;
import cn.wizzer.app.bus.modules.services.BusinessFileInfoService;
import cn.wizzer.app.bus.modules.services.YJbxxLzxxService;
import cn.wizzer.app.bus.modules.services.YJbxxService;
import cn.wizzer.app.sys.modules.models.Sys_unit;
import cn.wizzer.app.sys.modules.models.Sys_user;
import cn.wizzer.app.sys.modules.services.SysDictService;
import cn.wizzer.app.sys.modules.services.SysUnitService;
import cn.wizzer.app.sys.modules.services.SysUserService;
import cn.wizzer.app.web.commons.base.Globals;
import cn.wizzer.app.web.commons.doc.DocUtils;
import cn.wizzer.app.web.commons.slog.annotation.SLog;
import cn.wizzer.app.web.commons.utils.DateUtil;
import cn.wizzer.app.web.commons.utils.StringUtil;
import cn.wizzer.app.web.modules.controllers.open.file.UploadController;
import cn.wizzer.framework.base.Result;
import cn.wizzer.framework.page.Pagination;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.authz.annotation.RequiresAuthentication;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.jsoup.helper.DataUtil;
import org.nutz.dao.Cnd;
import org.nutz.dao.Sqls;
import org.nutz.dao.entity.Record;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.Strings;
import org.nutz.lang.Times;
import org.nutz.lang.util.NutMap;
import org.nutz.log.Log;
import org.nutz.log.Logs;
import org.nutz.mvc.annotation.At;
import org.nutz.mvc.annotation.Ok;
import org.nutz.mvc.annotation.Param;
import org.nutz.trans.Atom;
import org.nutz.trans.Trans;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.sql.Connection;
import java.util.*;


/**
 * 样品移交  虞秀斌  2019-11
 */
@IocBean
@At("/platform/bus/reports/task")
public class TaskCountController {
    private static final Log log = Logs.get();
    @Inject
    private YJbxxService yjbxxService;
    @Inject
    private SysDictService sysDictService;

    @At("/")
    @Ok("beetl:/platform/bus/reports/task/index.html")
    @RequiresAuthentication
    public void index(HttpServletRequest req) {
        req.setAttribute("jylxMap", JSONObject.fromObject(sysDictService.getCodeMap("jylx")));
        req.setAttribute("lcztMap", JSONObject.fromObject(sysDictService.getCodeMap("lczt")));
        req.setAttribute("startDate", DateUtil.getMonthBegin());
        req.setAttribute("endDate", DateUtil.getMonthEnd());
    }

    @At
    @Ok("json")
    @RequiresAuthentication
    public Object data(@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("type") String type, @Param("pageOrderName") String pageOrderName, @Param("pageOrderBy") String pageOrderBy) {
        try {
            String dateSql = "";
            if(StringUtils.isNotBlank(startDate)){
                dateSql+= " and b.opAt>="+DateUtil.getLongTime(startDate)+" ";
            }
            if(StringUtils.isNotBlank(endDate)){
                dateSql+= " and b.opAt<="+DateUtil.getLongTime(endDate)+" ";
            }
            String sql = "select * from (select case when x.numSum>0 then x.numSum else 0 end as numSum,x.unitid,ut.name,x.id,x.username from " +
                    " (select u.* ,sum(d.num) as numSum from sys_user u left join  (select a.*,1 as num from (select max(b.opAt) as opAt,b.ypbh " +
                    " from y_jbxx_lcxx b where b.lczt= 2  and b.delFlag=0 "+dateSql+" group by b.ypbh) c left join y_jbxx_lcxx a on a.ypbh = c.ypbh and a.opAt = c.opAt ) d  " +
                    " on d."+type+" like concat('%',u.id,'%') where u.disabled=0 and u.delFlag=0 group by u.id)  x left join sys_unit ut on x.unitid=ut.id where ut.sfjybm=1 )h";

           /* String sql = "SELECT CASE WHEN x.numSum > 0 THEN x.numSum ELSE 0  END AS numSum, x.unitid, ut.NAME, x.id, x.username\n" +
                    "            FROM ( select ul.id,ul.unitid,ul.username,(sum(ul.bznum)+sum(ul.jynum)) as numSum  from (SELECT\n" +
                    "                    d.ypbh, case when LOCATE(u.id,d.nextsprIds)>0 then 1 else 0 end as bznum, case when LOCATE(u.id,d.jyryIds)>0 then 1 else 0 end as jynum, u.*\n" +
                    "                    FROM sys_user u LEFT JOIN ( SELECT y.ypbh, l.nextsprIds, l.jyryIds, concat(l.nextsprIds,l.jyryIds) as ids\n" +
                    "                FROM y_jbxx y left join y_jbxx_lcxx l on l.jbxxId=y.id and l.delFlag=0 and l.lczt=2  AND l.opAt >= 1714492800  AND l.opAt <= 1717084800\n" +
                    "                where y.lczt>1 ) d ON d.ids LIKE concat( '%', u.id, '%' )) ul\n" +
                    "                GROUP BY ul.id  ) x LEFT JOIN sys_unit ut ON x.unitid = ut.id  WHERE ut.sfjybm = 1  ORDER BY x.unitid DESC, numSum DESC";*/

            if (Strings.isNotBlank(pageOrderName) && Strings.isNotBlank(pageOrderBy)) {
                if ("ascending".equalsIgnoreCase(pageOrderBy)) {
                    sql+= " order by "+pageOrderName+" asc ";
                } else {
                    sql+= " order by "+pageOrderName+" desc ";
                }
            }else{
                sql+= " order by unitid desc, numSum desc  ";
            }
            return Result.success().addData(yjbxxService.list( Sqls.create(sql)));
        } catch (Exception e) {
            return Result.error();
        }
    }
    @At
    @Ok("json")
    @RequiresAuthentication
    public Object jbxxData(@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("type") String type,@Param("searchKeyword") String searchKeyword,@Param("userid") String userid,@Param("pageNumber") int pageNumber,
                       @Param("pageSize") int pageSize, @Param("yppageOrderName") String pageOrderName, @Param("yppageOrderBy") String pageOrderBy) {
        try {

            String dateSql = "";
            if(StringUtils.isNotBlank(startDate)){
                dateSql+= " and b.opAt>="+DateUtil.getLongTime(startDate)+" ";
            }
            if(StringUtils.isNotBlank(endDate)){
                dateSql+= " and b.opAt<="+DateUtil.getLongTime(endDate)+" ";
            }
            String sql ="select y.* from (select max(b.opAt) as opAt,b.ypbh  from y_jbxx_lcxx b where b.lczt= 2  and b.delFlag=0  " +
                    dateSql+"  and b."+type+" like '%"+userid+"%' " +
                    "group by b.ypbh) c left join y_jbxx_lcxx a on a.ypbh = c.ypbh and a.opAt = c.opAt  left join y_jbxx y on a.jbxxId = y.id where 1=1 ";//nextsprIds

            if(StringUtils.isNotBlank(searchKeyword)){
                sql+= " and (y.ypbh like '%"+searchKeyword+"%' or y.ypmc like '%"+searchKeyword+"%')";
            }
            if (Strings.isNotBlank(pageOrderName) && Strings.isNotBlank(pageOrderBy)) {
                if ("ascending".equalsIgnoreCase(pageOrderBy)) {
                    sql+= " order by "+pageOrderName+" asc ";
                } else {
                    sql+= " order by "+pageOrderName+" desc ";
                }
            }else{
                sql+= " order by y.opAt desc ";
            }
            Pagination pagination  = yjbxxService.listPageMap(pageNumber,pageSize, Sqls.create(sql));
            return Result.success().addData(pagination);
        } catch (Exception e) {
            return Result.error();
        }
    }


}
